﻿using DigiHua.sMES.Backend.Provider.API.Implements;
using DigiHua.sMES.Backend.Provider.API.Models;
using DigiHua.sMES.Backend.Provider.Meta.Enums;
using DigiHua.sMES.Backend.Provider.Meta.Implements;
using DigiHua.sMES.Backend.Provider.Meta.Models;
using Microsoft.Extensions.DependencyInjection;
using Module_CUS.API.AccessoryRepair_C.Models;
using ModelQuery.Models;
using Newtonsoft.Json.Linq;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Module_Model.API.ModelQuery.Repositories;
using Module_Model.API.ModelCreate.Repositories;
using DigiHua.sMES.Backend.Provider.Common;
using System.Dynamic;
using Module_Model.API.ModelDelete.Repositories;
using System.Data;

namespace Module_CUS.API.AccessoryRepair_C.Repositories
{
    public class AccessoryRepair_CRepo : FlowRepository<AccessoryRepair_CModel>
    {
        public AccessoryRepair_CRepo(IServiceProvider serviceProvider, RepositoryInfo repositoryInfo) : base(serviceProvider, repositoryInfo)
        {

        }

        public override AccessoryRepair_CModel InitData()
        {
            var _model = this._modelProvider.GetRequestContent<AccessoryRepair_CModel>();
            return _model;
        }

        public override RepoExecuteResult ValidateData(AccessoryRepair_CModel model)
        {
            var _result = new RepoExecuteResult();
            model.entities.ForEach(_entity =>
            {
                if (!string.IsNullOrEmpty(_entity.ADDLIFETYPE) && string.IsNullOrEmpty(_entity.ADDLIFE))
                    _result.AddError(new RepositoryMessage($"未指定延長的值"));
                if (_entity.ReasonNo.Count == 0)
                    _result.AddError(new RepositoryMessage($"未選擇原因"));
            });


            return _result;
        }

        public override void HandleData(AccessoryRepair_CModel model)
        {
            var _StateLogMetaEntity = _serviceProvider.GetService<MetaManager>().GetMeta<ModelMetaEntity>("TBLEMSACCESSORYSTATELOG");//狀態LOG
            var _StateMetaEntity = _serviceProvider.GetService<MetaManager>().GetMeta<ModelMetaEntity>("TBLEMSACCESSORYSTATE");//狀態
            var _RepairMetaEntity = _serviceProvider.GetService<MetaManager>().GetMeta<ModelMetaEntity>("TBLEMSACCLOG_REPAIR");//維修結果記錄
            var _ContentRepairMetaEntity = _serviceProvider.GetService<MetaManager>().GetMeta<ModelMetaEntity>("TBLEMSACCCONTENT_REPAIR");//維修項目
            var _ReasonMetaEntity = _serviceProvider.GetService<MetaManager>().GetMeta<ModelMetaEntity>("TBLEMSACCESSORYSTATEREASON");//維修原因
            var _UsedMetaEntity = _serviceProvider.GetService<MetaManager>().GetMeta<ModelMetaEntity>("TBLEMSACCSUBSTITUTIONUSED");//使用備品
            model.entities.ForEach(_entity =>
            {

                //取模治具資料分類
                List<AccessoryBasisEntity> AccBasis = SelectData<AccessoryBasisEntity>("TBLEQPACCESSORYBASIS", JObject.FromObject(
                                                                                        new
                                                                                        {
                                                                                            ACCESSORYNO = _entity.ACCESSORYNO,
                                                                                            ACCESSORYVERSION = _entity.ACCESSORYVERSION,
                                                                                        }).ToObject<Dictionary<string, object>>());
                if (AccBasis.Count > 0)
                {
                    //取得最後原狀態
                    string strSQL = @"
                    select ACCSERIALNO, ACCESSORYSTATE
                    from TBLEMSACCESSORYSTATELOG
                    where ACCSERIALNO = (select max(ACCSERIALNO) from TBLEMSACCESSORYSTATELOG 
                                         where ACCESSORYNO =@ACCESSORYNO and ACCESSORYSTATE<>3)
                    ";

                    List<AccessoryStateLogEntity> LogState = _serviceProvider.GetDao().Query<AccessoryStateLogEntity>(strSQL, new { ACCESSORYNO = _entity.ACCESSORYNO });
                    int iState = 0;
                    if (LogState.Count > 0)
                        iState = LogState[0].ACCESSORYSTATE;
                    //取模治具現況
                    strSQL = @"
                    select ACCUMULATEQTY
                    from TBLEMSACCESSORYSTATE
                    where ACCESSORYNO=@ACCESSORYNO and ACCESSORYVERSION=@ACCESSORYVERSION
";

                    List<AccessoryStateEntity> AccState = _serviceProvider.GetDao().Query<AccessoryStateEntity>(strSQL, new { ACCESSORYNO = _entity.ACCESSORYNO, ACCESSORYVERSION = _entity.ACCESSORYVERSION });

                    //判斷是否啟用壽命管理
                    strSQL = @"
                    select LIFEMANAGE,ACCESSORY,SCRAPLIFE,REPAIRCYCLE
                    from TBLEQPACCESSORYLIFE 
                    where ACCESSORY = @ACCESSORY
                    ";
                    List<AccessoryLifeEntity> LifeState = _serviceProvider.GetDao().Query<AccessoryLifeEntity>(strSQL, new { ACCESSORY = _entity.ACCESSORYTYPE });
                    dynamic objLife = new ExpandoObject();
                    objLife.IsLifeManage = false;

                    objLife.intResidualLife = 0;
                    objLife.intAccumulateQty = 0;
                    objLife.intRepairCycle = 0;

                    if (LifeState.Count > 0 && LifeState[0].LIFEMANAGE == 1)
                    {
                        if (string.IsNullOrEmpty(_entity.ADDLIFETYPE))
                            throw new APIRuntimeException($"壽命類別 未指定");

                        objLife.IsLifeManage = true;
                        objLife.intResidualLife = Convert.ToInt32(LifeState[0].REPAIRCYCLE - AccState[0].ACCUMULATEQTY);//目前剩餘壽命
                        objLife.intAccumulateQty = Convert.ToInt32(AccState[0].ACCUMULATEQTY);//目前累計使用次數
                        objLife.intRepairCycle = Convert.ToInt32(LifeState[0].SCRAPLIFE);//壽命上限
                    }


                    //組合式治具也要能叫修, 以模治具分類取得是否為組合模治具資訊
                    var AccCatetory = SelectData<AccessoryCategoryEntity>("TBLEQPACCESSORYCATEGORY", JObject.FromObject(
                                                                                            new
                                                                                            {
                                                                                                ACCESSORYCATEGORY = AccBasis[0].ACCESSORYCATEGORY

                                                                                            }).ToObject<Dictionary<string, object>>());
                    //若為組合式模治具需要將此組合模治具編號下的所有模治具一併做狀態的變動
                    if (AccCatetory.FirstOrDefault().CombineACC == "1")
                    {
                        var CombineAccState = SelectData<CombineAccEntity>("TBLEMSCOMBINEACCSTATE", JObject.FromObject(
                                                                                            new
                                                                                            {
                                                                                                COMBINEACCNO = _entity.ACCESSORYNO
                                                                                            }).ToObject<Dictionary<string, object>>());
                        CombineAccState.ForEach(_combineAcc =>
                        {
                            //更新模治具狀態
                            UpdateAccessoryState(_StateLogMetaEntity, _combineAcc.AccessoryNo, null ,_entity, iState, objLife);
                        });
                    }
                    //更新選擇的模治具
                    UpdateAccessoryState(_StateLogMetaEntity, _entity.ACCESSORYNO, _entity.ACCESSORYVERSION, _entity, iState, objLife);
                    //維修結果記錄
                    GetRepository<ModelCreateRepo>().InsertData(_RepairMetaEntity, JObject.FromObject(
                                                                                        new
                                                                                        {
                                                                                            ACCSERIALNO = _entity.ACCSERIALNO,
                                                                                            ACCESSORYNO = _entity.ACCESSORYNO,
                                                                                            ACCESSORYVERSION = _entity.ACCESSORYVERSION,
                                                                                            REPAIRER = _modelProvider.GetClientInfo().account,
                                                                                            REPAIRTIME = _entity.RepairTime,
                                                                                            DESCRIPTION = _entity.DESCRIPTION
                                                                                        }).ToObject<Dictionary<string, object>>());
                    //維修項目記錄
                    var AccItem = SelectData<AccessoryRepairItem>("TBLEQPACCREPAIRITEM", JObject.FromObject(
                                                                                           new
                                                                                           {
                                                                                               ACCESSORYTYPE = _entity.ACCESSORYTYPE

                                                                                           }).ToObject<Dictionary<string, object>>());
                    if (AccItem.Count > 0)
                    {
                        AccItem.ForEach(_Item =>
                        {
                            GetRepository<ModelCreateRepo>().InsertData(_ContentRepairMetaEntity, JObject.FromObject(
                                                                                       new
                                                                                       {
                                                                                           ACCSERIALNO = _entity.ACCSERIALNO,
                                                                                           ACCESSORYNO = _entity.ACCESSORYNO,
                                                                                           ACCESSORYVERSION = _entity.ACCESSORYVERSION,
                                                                                           REPAIRITEMNO = _Item.REPAIRITEMNO,
                                                                                           REPAIRITEMNAME = _Item.REPAIRITEMNAME,
                                                                                       }).ToObject<Dictionary<string, object>>());

                        });

                    }

                    //原因記錄
                    //var _AccStateReasonMetaEntity = _serviceProvider.GetService<MetaManager>().GetMeta<ModelMetaEntity>("TBLEMSACCESSORYSTATEREASON");
                    _entity.ReasonNo.ForEach(_reson =>
                        {
                            GetRepository<ModelCreateRepo>().InsertData(_ReasonMetaEntity, JObject.FromObject(
                                                                                           new
                                                                                           {
                                                                                               ACCSERIALNO = _entity.ACCSERIALNO,
                                                                                               ACCESSORYNO = _entity.ACCESSORYNO,
                                                                                               ACCESSORYVERSION = _entity.ACCESSORYVERSION,
                                                                                               ORGACCSTATE = 3,
                                                                                               NEWACCSTATE = iState,
                                                                                               REASONNO = _reson.REASONNO,
                                                                                               REASONNAME = _reson.REASONNAME
                                                                                           }).ToObject<Dictionary<string, object>>());

                        });
                    _entity.SUBSTITUTIONNO.ForEach(_used =>
                    {
                        //使用備品記錄
                        GetRepository<ModelCreateRepo>().InsertData(_UsedMetaEntity, JObject.FromObject(
                                                                                           new
                                                                                           {
                                                                                               ACCSERIALNO = _entity.ACCSERIALNO,
                                                                                               ACCESSORYNO = _entity.ACCESSORYNO,
                                                                                               ACCESSORYVERSION = _entity.ACCESSORYVERSION,
                                                                                               USEMODE = 1,
                                                                                               PLANNO = "N/A",
                                                                                               FREQUENCY = 0,
                                                                                               PMSERIALNO = "N/A",
                                                                                               SUBSTITUTIONNO = _used.SUBSTITUTIONNO,
                                                                                               USEQTY = 0,
                                                                                           }).ToObject<Dictionary<string, object>>());
                    });

                }
            }
            );

        }

        public override dynamic ReturnData(AccessoryRepair_CModel model)
        {
            return null;
        }
        private List<T> SelectData<T>(string TableName, Dictionary<string, object> Parameters)
        {
            var _conditions = new List<ConditionMetaEntity>();

            foreach (var item in Parameters)
            {
                var _c = new ConditionMetaEntity(item.Key);
                _c.conditionType = ConditionType.And;
                _c.value = item.Value;

                _conditions.Add(_c);
            }

            var _modelQueryModel = new ModeQueryModel() { targetName = TableName.ToUpper(), conditions = _conditions };

            return GetRepository<ModelQueryRepo>().ReturnData<T>(_modelQueryModel);
        }
        private void UpdateAccessoryState(ModelMetaEntity _AccStateLogMetaEntity, string AccessoryNo, string AccessoryVersion, AccessoryRepairEntity _entity, int iNewState, dynamic objLife)
        {
            //將目前的現況轉至Log
            var AccessoryState = _serviceProvider.GetDao().Query<AccessoryRepairEntity>(@"
                                    Select a.ACCSerialNo,a.AccessoryState,a.StartTime,a.UserNo,a.Description,
                                            a.AccessoryNo,a.AccessoryVersion,a.APPLYTIME,a.ACCUMULATEQTY,b.* 
                                    From tblEMSAccessoryState a, TBLEQPACCESSORYBASIS b 
                                    Where a.AccessoryNo = @AccessoryNo 
                                        And a.AccessoryVersion = (select AccessoryVersion from TBLEQPACCESSORYBASIS where AccessoryNo = @AccessoryNo and CURVERSION = 1) 
                                        And a.AccessoryNo = b.AccessoryNo and a.AccessoryVersion = b.AccessoryVersion", new { AccessoryNo = AccessoryNo });
            AccessoryState.ForEach(_AccState =>
            {
                //記錄Log

                dynamic param = new ExpandoObject();
                param.ACCSERIALNO = _AccState.ACCSERIALNO;
                param.ACCESSORYNO = _AccState.ACCESSORYNO;
                param.ACCESSORYVERSION = _AccState.ACCESSORYVERSION;
                param.ACCESSORYSTATE = _AccState.ACCESSORYSTATE;
                param.ACCESSORYTYPE = _AccState.ACCESSORYTYPE;
                param.USERNO = _AccState.USERNO;
                param.STARTTIME = _AccState.STARTTIME;
                param.ENDTIME = DateTime.Now;
                param.DESCRIPTION = _AccState.DESCRIPTION;
                param.APPLYTIME = _AccState.APPLYTIME;

                int intACCUMULATEQTY = objLife.intAccumulateQty; //目前累計使用次數
                decimal intNewAccumQty = 0;
                if (objLife.IsLifeManage)
                {
                    param.ADDLIFETYPE = _entity.ADDLIFETYPE;
                    param.ADDLIFE = _entity.ADDLIFE;

                    intNewAccumQty = GetLifeLimitAndAddLifeNumOfVal(_entity.ADDLIFETYPE, _entity.ADDLIFE, objLife.intAccumulateQty, objLife.intRepairCycle);
                    decimal intRealAddLife = intNewAccumQty;
                    //為負時歸0
                    if (intNewAccumQty < 0)
                    {
                        intNewAccumQty = 0;
                        intRealAddLife = intACCUMULATEQTY;
                    }
                    //大於原累計使用次數, 則等同原累計使用次數
                    else if (intNewAccumQty > intACCUMULATEQTY)
                    {
                        intNewAccumQty = intACCUMULATEQTY;
                        intRealAddLife = 0;
                    }
                    else
                        intRealAddLife = Math.Abs(intACCUMULATEQTY - intRealAddLife);
                    param.REALADDLIFE = intRealAddLife;
                    param.ACCUMULATEQTY = intNewAccumQty;
                }

                // 防呆
                GetRepository<ModelDeleteRepo>().DeleteData(JObject.FromObject(
                                                                                    param).ToObject<Dictionary<string, object>>(), null, _AccStateLogMetaEntity);

                GetRepository<ModelCreateRepo>().InsertData(_AccStateLogMetaEntity, JObject.FromObject(
                                                                                    param).ToObject<Dictionary<string, object>>());

                //Update 預期完修時間、外包商編號
                var SQL = new StringBuilder(@"Update tblEMSAccessoryState 
                                              Set AccessoryState = @AccessoryState, StartTime = @Now, ACCSerialNo = @ACCSerialNo,
                                              UserNo = @UserNo, Description = @Description
                                              ,ExpectRepairFinishDate=null,PlanRepairFinishDate = null,PlanRepairer = null,SubcontractorNo = null
                                              ");
                if (objLife.IsLifeManage)
                {
                    SQL.Append(" ,AccRepairQty = AccRepairQty + 1");
                    SQL.Append(" ,AccumulateQty = " + Convert.ToString(intNewAccumQty));
                    SQL.Append(" , ACCTotalUsedQty = " + Convert.ToString(intNewAccumQty));

                }
                SQL.Append(" Where AccessoryNo = @AccessoryNo And AccessoryVersion = @AccessoryVersion ");
                string _SQL = Convert.ToString(SQL);

                string sSerialNO = InitACCSerialNo();
                _serviceProvider.GetDao().ExecuteNonQuery(_SQL,
                                                                                new
                                                                                {
                                                                                    AccessoryNo = _AccState.ACCESSORYNO,
                                                                                    AccessoryVersion = _AccState.ACCESSORYVERSION,
                                                                                    AccessoryState = iNewState,
                                                                                    Now = DateTime.Now,
                                                                                    ACCSerialNo = sSerialNO,
                                                                                    UserNo = _entity.USERNO,
                                                                                    Description = _entity.DESCRIPTION,

                                                                                });

                // 更新模治具上下模次数 + 维修状态
                string cusSQL = " select count(1) from TBLCUSMoldMoves where AccessoryNo = @AccessoryNo and AccessoryVersion = @AccessoryVersion ";
                int isExists = 0;
                if (string.IsNullOrEmpty(AccessoryVersion))
                    isExists = _serviceProvider.GetDao().Query1Record<int>(cusSQL, new { AccessoryNo = _AccState.ACCESSORYNO, AccessoryVersion = _AccState.ACCESSORYVERSION });
                else
                    isExists = _serviceProvider.GetDao().Query1Record<int>(cusSQL, new { AccessoryNo = AccessoryNo, AccessoryVersion = AccessoryVersion });

                if (isExists > 0)
                {
                    cusSQL = @" update TBLCUSMoldMoves
                                set CurrentMoldUps = @CurrentMoldUps,CurrentMoldDowns = @CurrentMoldDowns,
                                    repairStatus = @repairStatus,modifiedDate = @modifiedDate
                                where AccessoryNo = @AccessoryNo and AccessoryVersion = @AccessoryVersion ";
                    if (string.IsNullOrEmpty(AccessoryVersion))
                    {
                        _serviceProvider.GetDao().ExecuteNonQuery(cusSQL,
                                                                               new
                                                                               {
                                                                                   CurrentMoldUps = 0,
                                                                                   CurrentMoldDowns = 0,
                                                                                   repairStatus = "Repaired",
                                                                                   modifiedDate = DateTime.Now,
                                                                                   AccessoryNo = _AccState.ACCESSORYNO,
                                                                                   AccessoryVersion = _AccState.ACCESSORYVERSION
                                                                               });
                    }
                    else
                    {
                        cusSQL += "  ";
                        _serviceProvider.GetDao().ExecuteNonQuery(cusSQL,
                                                                               new
                                                                               {
                                                                                   CurrentMoldUps = 0,
                                                                                   CurrentMoldDowns = 0,
                                                                                   repairStatus = "Repaired",
                                                                                   modifiedDate = DateTime.Now,
                                                                                   AccessoryNo = AccessoryNo,
                                                                                   AccessoryVersion = AccessoryVersion
                                                                               });
                    }
                }
                else
                {
                    cusSQL = @" insert into TBLCUSMoldMoves(Creator,CreateDate,AccessoryNo,AccessoryVersion,
                                                            HistoryCount,CurrentMoldUps,HistoricalMoldDowns,CurrentMoldDowns,
                                                            repairStatus,FutureUse,modifiedDate)
                                VALUES (@Creator,Getdate(),@AccessoryNo,@AccessoryVersion,
                                        0,0,0,0,
                                        'Repaired',0,Getdate()) ";
                    if (string.IsNullOrEmpty(AccessoryVersion))
                    {
                        _serviceProvider.GetDao().ExecuteNonQuery(cusSQL,
                                                                               new
                                                                               {
                                                                                   Creator = _modelProvider.GetClientInfo().account,
                                                                                   AccessoryNo = _AccState.ACCESSORYNO,
                                                                                   AccessoryVersion = _AccState.ACCESSORYVERSION
                                                                               });
                    }
                    else
                    {
                        cusSQL += "  ";
                        _serviceProvider.GetDao().ExecuteNonQuery(cusSQL,
                                                                               new
                                                                               {
                                                                                   Creator = _modelProvider.GetClientInfo().account,
                                                                                   AccessoryNo = AccessoryNo,
                                                                                   AccessoryVersion = AccessoryVersion
                                                                               });
                    }
                }
            });
        }
        // 20230825 Modify by duzm for M#144409:模治具增加寿命太大导致Int溢出
        private decimal GetLifeLimitAndAddLifeNumOfVal(string _type, string AccLifeNum, int intAccumulateQty, int intRepairCycle)
        {
            decimal variable = 0;
            decimal NewAccLife = 0;
            decimal decAccLifeNum = Convert.ToDecimal(AccLifeNum);
            int LiftType = Convert.ToInt32(_type);


            if (LiftType == 1 || LiftType == 3)
                NewAccLife = Math.Round(decAccLifeNum * intRepairCycle / 100);
            else if (LiftType == 2 || LiftType == 4)
                NewAccLife = decAccLifeNum;
            else
                throw new RepositoryRuntimeException("Unknown");

            //如為延長至固定數/百分比, 壽命上限-預計延長壽命量
            if (LiftType == 1 || LiftType == 2)
                variable = intRepairCycle - NewAccLife;
            //如為壽命增加固定數/百分比, 原使用次數-預計延長壽命量
            else
                variable = intAccumulateQty - NewAccLife;


            return variable;
        }

        /// <summary>
        /// 將產生ACC Serial
        /// </summary>
        /// <returns></returns>
        private string InitACCSerialNo()
        {
            string strYear, strMonth;
            var strTempDate = new object();
            var SerialNo_ACCSTATELOG = new object();
            var SerialNo_ACCSTATEDATE = new object();
            StringBuilder sbSQL = new StringBuilder();
            int intSerial = 1;
            strYear = DateTime.Now.Year.ToString().Substring(2, 2); //2018 取18
            strMonth = DateTime.Now.Month.ToString("d2");

            if (_serviceProvider.GetDao().setting.DbType == DigiHua.sMES.Backend.Provider.UnitOfWork.Enums.DbType.Oracle)
            {

                //'1.檢查Sequenct物件是否存在
                string sequenceName = "ACCSTATELOG" + strYear + strMonth;
                sbSQL.Clear();
                sbSQL.Append("Select Object_Name from User_Objects Where Object_Type = 'SEQUENCE' And Object_Name = '" + sequenceName + "'");
                DataTable dtTmp = _serviceProvider.GetDao().Query(sbSQL.ToString());

                if (dtTmp == null || dtTmp.Rows.Count == 0)
                {
                    //0071379: 模治具上下模出现问题
                    sbSQL.Clear();
                    sbSQL.Append("Create Sequence " + sequenceName + " Increment by 1 " + " Start with 1 " + " MaxValue 99999999 MinValue 0 Cycle Cache 20");

                    //'呼叫執行SQL指令
                    _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());
                }
                dtTmp.Clear();

                //'2.取得Sequence的流水號
                sbSQL.Clear();
                sbSQL.Append("Select " + sequenceName + ".NextVal as NextValue From Dual");
                dtTmp = _serviceProvider.GetDao().Query(sbSQL.ToString());

                if (dtTmp != null && dtTmp.Rows.Count > 0) intSerial = Convert.ToInt32(dtTmp.Rows[0]["NextValue"]);
                else
                {
                    throw new Exception($"Get NextVal of sequence object:{ sequenceName } fail !");
                }
            }
            else
            {
                sbSQL.Clear();
                sbSQL.Append("Select SerialNo From tblSysSerialNo Where FunctionName = 'ACCSTATEDATE'");
                SerialNo_ACCSTATEDATE = _serviceProvider.GetDao().Query1RecordColumn<string>(sbSQL.ToString());

                //防呆
                if (SerialNo_ACCSTATEDATE == null)
                {
                    //'填入新的日期,格式化存入(ex:0412,因Serial為數值故無法存入0412,故一律前面加上1)
                    sbSQL.Clear();
                    sbSQL.Append("Insert Into tblSysSerialNo (FunctionName, SerialNo) Values ('ACCSTATEDATE',1" + strYear + strMonth + ")");
                    //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                    _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());

                    // '月的序號
                    sbSQL.Clear();
                    sbSQL.Append("Select SerialNo From tblSysSerialNo Where FunctionName = 'ACCSTATELOG'");
                    SerialNo_ACCSTATELOG = _serviceProvider.GetDao().Query1RecordColumn<string>(sbSQL.ToString());

                    if (SerialNo_ACCSTATELOG == null)
                    {
                        sbSQL.Clear();
                        sbSQL.Append("Insert Into tblSysSerialNo (FunctionName, SerialNo) Values ('ACCSTATELOG', 1)");
                        //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                        _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());
                    }
                    else
                    {
                        sbSQL.Clear();
                        sbSQL.Append("Update tblSysSerialNo Set SerialNo = 1 Where FunctionName = 'ACCSTATELOG'");
                        //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                        _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());
                    }

                    intSerial = 1;
                }
                else
                {
                    strTempDate = SerialNo_ACCSTATEDATE;

                    //'更新日期,格式化存入(ex:041208,因Serial為數值故無法存入041208,故一律前面加上1)
                    sbSQL.Clear();
                    sbSQL.Append("Update tblSysSerialNo Set SerialNo = 1" + strYear + strMonth + " Where FunctionName = 'ACCSTATEDATE'");
                    //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                    _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());

                    // '比較日期

                    if ((strTempDate.ToString()).Equals("1" + strYear + strMonth)) //'同月
                    {
                        // '取出月的序號
                        sbSQL.Clear();
                        sbSQL.Append("Select SerialNo From tblSysSerialNo Where FunctionName = 'ACCSTATELOG'");
                        SerialNo_ACCSTATELOG = _serviceProvider.GetDao().Query1RecordColumn<string>(sbSQL.ToString());
                        if (SerialNo_ACCSTATELOG == null)
                        {
                            sbSQL.Clear();
                            sbSQL.Append("Insert Into tblSysSerialNo (FunctionName, SerialNo) Values ('ACCSTATELOG', 1)");
                            //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                            _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());

                            intSerial = 1;
                        }
                        else
                        {
                            // '填入新SerialNo
                            sbSQL.Clear();
                            sbSQL.Append("Update tblSysSerialNo Set SerialNo = " + (Convert.ToInt32(SerialNo_ACCSTATELOG) + 1) + " Where FunctionName = 'ACCSTATELOG'");
                            //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                            _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());

                            intSerial = (Convert.ToInt32(SerialNo_ACCSTATELOG) + 1);
                        }
                    }
                    else
                    {
                        //'月的序號
                        sbSQL.Clear();
                        sbSQL.Append("Select SerialNo From tblSysSerialNo Where FunctionName = 'ACCSTATELOG'");
                        SerialNo_ACCSTATELOG = _serviceProvider.GetDao().Query1RecordColumn<string>(sbSQL.ToString());

                        if (SerialNo_ACCSTATELOG == null)
                        {
                            sbSQL.Clear();
                            sbSQL.Append("Insert Into tblSysSerialNo (FunctionName, SerialNo) Values ('ACCSTATELOG', 1)");
                            //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                            _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());
                        }
                        else
                        {
                            sbSQL.Clear();
                            sbSQL.Append("Update tblSysSerialNo Set SerialNo = 1 Where FunctionName = 'ACCSTATELOG'");
                            //20210525 Modify by Tim for#XXX：[效能]立即異動表格，預防表鎖.
                            _serviceProvider.GetDao().ExecuteImmediately(sbSQL.ToString());
                        }
                        intSerial = 1;
                    }
                }
            }

            // 'ACC Serial No編碼規則
            // '一碼前置字元 +　西元年後兩碼　＋ 兩碼月份 + 七碼流水號
            // '每月流水號歸零，例如S06010000001

            string ACCSerialNo = "A" + strYear + strMonth + intSerial.ToString("0000000");
            return ACCSerialNo;
        }
    }
}
